﻿using System;
using System.Collections.Generic;
using System.Text;
using System.Data.SqlClient;
using System.Data;
using System.IO;
using System.Windows.Forms;

public static class SqlHelper
{
    private static SqlConnection sqlCon = null;

    public static bool OpenSqlCon()
    {
        try
        {
            if (sqlCon != null)
            {
                if (sqlCon.State != ConnectionState.Open)
                    sqlCon.Open();
            }
            else
            {
                string linkSql = System.Configuration.ConfigurationManager.AppSettings["ConnStr"];
                sqlCon = new SqlConnection(linkSql);
                sqlCon.Open();
            }

            return true;
        }
        catch (Exception ex)
        {
            Log.writeExceptionLog("Open Db Connection Exception: " + ex.Message);
            return false;
        }
    }

    public static void ExecSqlCmdSql(string sql)
    {
        try
        {
            if (OpenSqlCon())
            {
                SqlCommand cmd = new SqlCommand(sql, sqlCon);
                cmd.ExecuteNonQuery();
                if (sqlCon.State != ConnectionState.Closed)
                    sqlCon.Close();
            }
        }
        catch (Exception ex)
        {
            Log.writeExceptionLog("Db ExecSqlCmdSql Exception: " + ex.Message);
            Log.writeExceptionLog("Exception sql: " + sql);
        }
    }

    public static bool ExecSqlCmdSqlEx(string sql)
    {
        try
        {
            if (OpenSqlCon())
            {
                SqlCommand cmd = new SqlCommand(sql, sqlCon);
                cmd.ExecuteNonQuery();
                if (sqlCon.State != ConnectionState.Closed)
                    sqlCon.Close();
                return true;
            }
        }
        catch (Exception ex)
        {
            Log.writeExceptionLog("Db ExecSqlCmdSql Exception: " + ex.Message);
            Log.writeExceptionLog("Exception sql: " + sql);
            return false;
        }
        return false;
    }

    public static string GetDataStringSql(string sql)
    {
        try
        {
            string sRtn = "";

            if (OpenSqlCon())
            {
                SqlCommand cmd = new SqlCommand(sql, sqlCon);
                SqlDataReader rd = cmd.ExecuteReader();
                if (rd.Read())
                    sRtn = rd[0].ToString();

                rd.Close();
                if (sqlCon.State != ConnectionState.Closed)
                    sqlCon.Close();
            }
            return sRtn;
        }
        catch (Exception ex)
        {
            Log.writeExceptionLog("Db GetDataStringSql Exception: " + ex.Message);
            Log.writeExceptionLog("Exception sql: " + sql);
            return "";
        }
    }

    public static DataTable GetDataTableSql(string sql)
    {
        try
        {
            if (!OpenSqlCon())
            {
                return null;
            }
            else
            {
                SqlDataAdapter ad = new SqlDataAdapter(sql, sqlCon);
                DataTable dt = new DataTable();
                ad.Fill(dt);

                if (sqlCon.State != ConnectionState.Closed)
                    sqlCon.Close();
                return dt;
            }
        }
        catch (Exception ex)
        {
            Log.writeExceptionLog("Db GetDataTableSql Exception: " + ex.Message);
            Log.writeExceptionLog("Exception sql: " + sql);
            return null;
        }
    }
}

